﻿using CloudDcPc.Entity;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CloudDcPc.Bean
{
    public class TogetherBean
    {
        private MySqlConnection myConn = null;
        private DBConnection dbc = null;
        private MySqlCommand cmd = null;
        public TogetherBean()
        {
            this.dbc = new DBConnection();
            this.myConn = this.dbc.getConnection();
        }

        public DataTable GetOrdersByName(string name)
        {
            string sql = "select food_name 品名,count 数量,unit 单位,m.price 单价（元） from dc_together t join dc_menu m on m.food_num=t.menuid where name='"+name+"'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }

        public bool AddTg(Together tg, ObservableCollection<MyMenu> list)
        {
            string sql = "";
            if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
            MySqlTransaction myTran = myConn.BeginTransaction();
            try
            {
                foreach (MyMenu item in list)
                {
                    if (float.Parse(item.CountInTg) > 0)
                    {
                        sql = "insert into dc_together(name,menuid,count,price) values('" + tg.Name + "','" + item.Food_num + "','" + item.CountInTg + "','" + tg.Price + "')";
                        cmd = new MySqlCommand(sql, myConn);
                        cmd.ExecuteNonQuery();
                    }
                }
                if (sql == "")
                {
                    myTran.Rollback();
                    return false;
                }
                myTran.Commit();
                return true;
            }
            catch
            {
                myTran.Rollback();
                return false;
            }
            finally
            {
                dbc.close();
            }
        }

        public bool UpdateTg(string name, Together tg, ObservableCollection<MyMenu> list)
        {
            string sql = "delete from dc_together where name='" + name + "'";
            string sql2 = "";

            if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
            MySqlTransaction myTran = myConn.BeginTransaction();
            try
            {

                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                foreach (MyMenu item in list)
                {
                    if (item.CountInTg != null)
                    {
                        if (float.Parse(item.CountInTg) > 0)
                        {
                            sql2 = "insert into dc_together(name,menuid,count,price) values('" + tg.Name + "','" + item.Food_num + "','" + item.CountInTg + "','" + tg.Price + "')";
                            cmd = new MySqlCommand(sql2, myConn);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                if (sql2 == "")
                {
                    myTran.Rollback();
                    return false;
                }
                myTran.Commit();
                return true;
            }
            catch
            {
                myTran.Rollback();
                return false;
            }
            finally
            {
                dbc.close();
            }
        }

        public ObservableCollection<MyMenu> GetInMyMenu(string tg_name)
        {
            MyMenu myMenu;
            ObservableCollection<MyMenu> list = new ObservableCollection<MyMenu>();
            string sql = "select dm.*,dt.count count from dc_together dt join dc_menu dm  on dt.menuid=dm.food_num where name='" + tg_name + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    myMenu = new MyMenu()
                    {
                        Id = (int)reader["id"],
                        Food_name = reader["food_name"].ToString(),
                        Food_num = reader["food_num"].ToString(),
                        Price = (float)reader["price"],
                        Type = reader["type"].ToString(),
                        Unit = reader["unit"].ToString(),
                        Enough = (int)reader["enough"],
                        CountInTg = reader["count"].ToString()
                    };
                    list.Add(myMenu);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }

        public ObservableCollection<MyMenu> GetNotInMyMenu()
        {
            MyMenu myMenu;
            ObservableCollection<MyMenu> list = new ObservableCollection<MyMenu>();
            // string sql = "select * from dc_menu where id not in(select menuid from dc_together where name='" + tg_name + "')";
            string sql = "select * from dc_menu order by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    myMenu = new MyMenu()
                    {
                        Id = (int)reader["id"],
                        Food_name = reader["food_name"].ToString(),
                        Food_num = reader["food_num"].ToString(),
                        Price = (float)reader["price"],
                        Type = reader["type"].ToString(),
                        Unit = reader["unit"].ToString(),
                        Enough = (int)reader["enough"],
                    };
                    list.Add(myMenu);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }

        public bool HasTogether(string name)
        {
            string sql = "select id from dc_together where name='" + name + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool DelTogetherById(int id)
        {
            string sql = "delete from dc_together where id='" + id + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }

        public ObservableCollection<Together> GetAllTogether()
        {
            ObservableCollection<Together> list = new ObservableCollection<Together>();
            Together together = null;
            string sql = "select * from dc_together group by name order by price DESC";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    together = new Together()
                    {
                        Id = (int)reader["id"],
                        Name = reader["name"].ToString(),
                        Price = (float)reader["price"]
                    };
                    list.Add(together);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }

        public Together GetTogetherByField(string f, string w)
        {
            Together together = null;
            string sql = "select * from dc_together where " + f + "='" + w + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    together = new Together()
                    {
                        Id = (int)reader["id"],
                        Name = reader["name"].ToString(),
                        Price = (float)reader["price"]
                    };

                }
                return together;
            }
            finally
            {
                dbc.close();
            }
        }
    }
}

